﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class FLTORDNETWORKData : BaseFunction
    {
        
        public FLTORDNETWORKData()
            : base()
        {

        }
        public FLTORDNETWORKData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKPOS";
            string ORDER = dwABPOS["ORDER"].ToString().Trim();
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string CUSTOMER = dwABPOS["CUSTOMER"].ToString().Trim();
            string FROMDATE = dwABPOS["fromdate"].ToString().Trim();
            string ENDDATE = dwABPOS["enddate"].ToString().Trim();
            /*"SELECT * FROM CO_DELAY_REASON A "
                   + " WHERE (TO_NUMBER(A.ORDERNO) = '" + ORDER + "' OR '" + ORDER + "' = '999999') "
                   + " AND (TRIM(A.ARTICLE) = '" + ARTICLE + "' OR '" + ARTICLE + "' = 'AAA') "
                   + " AND A.DELDATE >='" + FROMDATE + "' AND A.DELDATE <= '" + ENDDATE + "' "
                   + " AND (TO_NUMBER(A.CUSTOMERCODE) = '" + CUSTOMER + "' OR '" + CUSTOMER + "'='99999999') "
                   + " ORDER BY 1,5,6 " */
            
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TO_DATE(A.VKPLIEFTERM,'YYYYMMDD')-TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') AS DD, "
                            + " to_char(to_date(A.VKPLIEFTERM,'yyyymmdd'),'dd/Mon/yy') AS DELDATEF, "
                            + "  A.VKPLIEFTERM AS DELDATE, "
                            + "  to_char(to_date(A.VKPNEUDAT,'yyyymmdd'),'dd/Mon/yy') AS ORDERDATE, "
                            + "  B.KDKUNDNA AS CUSTOMER, A.VKPAUFLFD AS ORDERNO,A.VKPAUFPOS as item, "
                            + "  A.VKPNEUBEN AS ENTERUSER,A.VKPART AS ARTICLE,A.VKPFARBE AS COLOR, "
                            + "  A.VKPMENGE1*0.01 AS ORDQTY, A.VKPZUGET*0.01 AS ALLQTY, "
                            + "  A.VKPVERS*0.01 AS SHIPQTY,C.ORDNO2,C.ALLQTY2, "
                            + "  DECODE(C.SPDATE2,'0','0',to_char(to_date(C.SPDATE2,'yyyymmdd'),'dd/Mon/yy')) AS SPDATE2, "
                            + "  DECODE(C.ALLDATE2,'0','0',to_char(to_date(C.ALLDATE2,'yyyymmdd'),'dd/Mon/yy')) AS ALLDATE2, "
                            + "  DECODE(C.PFDATE2,'0','0',to_char(to_date(C.PFDATE2,'yyyymmdd'),'dd/Mon/yy'))AS PFDATE2, "
                            + "  C.ORDSTATUS2, "
                            + "  DECODE(C.FDATE2,'0','0',to_char(to_date(C.FDATE2,'yyyymmdd'),'dd/Mon/yy'))AS FDATE2, "
                            + "  C.FINISHQTY2,C.ORDNO1,C.ALLQTY1, "
                            + "  DECODE(C.SPDATE1,'0','0',to_char(to_date(C.SPDATE1,'yyyymmdd'),'dd/Mon/yy')) AS SPDATE1, "
                            + "  DECODE(C.ALLDATE1,'0','0',to_char(to_date(C.ALLDATE1,'yyyymmdd'),'dd/Mon/yy'))AS ALLDATE1, "
                            + "  DECODE(C.PFDATE1,'0','0',to_char(to_date(C.PFDATE1,'yyyymmdd'),'dd/Mon/yy'))AS PFDATE1, "
                            + "  C.RECSTATUS1, "
                            + "  DECODE(C.RECDATE1,'0','0',to_char(to_date(C.RECDATE1,'yyyymmdd'),'dd/Mon/yy'))AS RECDATE1, "
                            + "  C.RECQTY1,C.ORDSTATUS1, "
                            + "  DECODE(C.FDATE1,'0','0',to_char(to_date(C.FDATE1,'yyyymmdd'),'dd/Mon/yy'))AS FDATE1, "
                            + "  C.FINISHQTY1,(CASE WHEN NVL(TRIM(G.REMARK),'-')='-' THEN H.REMARK ELSE G.REMARK||CHR(10)||H.REMARK  END) AS REMARK "
                            + "  FROM VKPOS A,KDSTAMM B,order_network c, "
                            + "  (SELECT distinct SUBSTR(A.TXTISIND,3,6) AS FLFD,SUBSTR(A.TXTISIND,9,3) AS FPOS,  "
                            + "   STRAGG(trim(B.TXTKTEXT))over(partition by TXTISIND) AS REMARK  "
                            + "   FROM TXTIND A,TXTKON B WHERE A.TXTIMANDANT = B.TXTKMANDANT   "
                            + "   AND A.TXTIPIND = B.TXTKPIND AND A.TXTITXTART = 'VKT'  "
                            + "   AND SUBSTR(A.TXTISIND,1,2) = '  '   AND SUBSTR(A.TXTISIND,12,4) = 'IVDI') H,  "
                            + "   (SELECT distinct SUBSTR(A.TXTISIND,3,6) AS FLFD, "
                            + "   STRAGG(trim(B.TXTKTEXT))over(partition by SUBSTR(A.TXTISIND,3,6)) AS REMARK  "
                            + "   FROM TXTIND A,TXTKON B WHERE A.TXTIMANDANT = B.TXTKMANDANT   "
                            + "   AND A.TXTIPIND = B.TXTKPIND AND A.TXTITXTART = 'VKT' AND SUBSTR(A.TXTISIND,9,3) ='000'  "
                            + "   AND SUBSTR(A.TXTISIND,1,2) = '  '   AND SUBSTR(A.TXTISIND,12,4) = 'IVDI') G "
                            + "  WHERE A.VKPMANDANT = B.KDMANDANT  AND A.VKPKUNDNR = B.KDKUNDNR "
                            + "  and a.vkpaufkrs = c.ABAVKAUFKRS(+) and a.vkpauflfd = c.ABAVKAUFLFD(+) "
                            + "  and a.vkpaufpos = c.ABAVKAUFPOS(+) AND A.VKPAUFLFD = H.FLFD(+)  "
                            + "  AND A.VKPAUFPOS = H.FPOS(+) AND A.VKPAUFLFD = G.FLFD(+) "
                            + "  AND A.VKPSA <>'AJ' AND A.VKPMANDANT = '00'  "
                            + "  AND A.VKPERL = ' ' " 
                    + " AND (TO_NUMBER(A.VKPAUFLFD) = '" + ORDER + "' OR '" + ORDER + "' = '999999') "
                    + " AND (TRIM(A.VKPART) = '" + ARTICLE + "' OR '" + ARTICLE + "' = 'AAA') "
                    + " AND (TO_NUMBER(A.VKPKUNDNR) = '" + CUSTOMER + "' OR '" + CUSTOMER + "'='99999999') "
                    + " AND A.VKPLIEFTERM >='" + FROMDATE + "' AND A.VKPLIEFTERM <= '" + ENDDATE + "' "
                    + " ORDER BY 1,4,5 "                   
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                         
                        //GetREMARK(dw);
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        /*public DataSet GetREMARK(DataRow dwUI)
        {
            string TableName = "REMARK";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string VKPAUFLFD = dwUI["ORDERNO"].ToString();
            string VKPAUFPOS = dwUI["VKPAUFPOS"].ToString();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT B.TXTKTEXT FROM TXTIND A,TXTKON B "
                    + " WHERE A.TXTIMANDANT = B.TXTKMANDANT "
                    + " AND A.TXTIPIND = B.TXTKPIND "
                    + " AND SUBSTR(A.TXTISIND,1,2) = '  ' "
                    + " AND SUBSTR(A.TXTISIND,3,6) = '"+VKPAUFLFD+"' "
                    + " AND SUBSTR(A.TXTISIND,9,3) = '"+VKPAUFPOS+"' "
                    + " AND SUBSTR(A.TXTISIND,12,4) = 'IVDI' "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    string remark = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "TXTKTEXT";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        remark = remark+ value + Environment.NewLine;
                        #endregion


                    }
                    dwUI["REMARK"] = remark;
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }*/
        public DataSet BuildFLTORDNETWORKData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKPOS(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}